package com.mrd.shiptask_shipowner.constants.database;

import org.json.JSONArray;
import org.json.JSONObject;

import com.mrd.shiptask_shipowner.unit_tools.DatasTools;

import android.content.Context;
import android.database.Cursor;

public class DBUtil {

	/**
	 * 插入前删除表格数据
	 */
	public static void deleteAll(Context ctx, String tableName) {
		String sql = "DELETE FROM '" + tableName + "'";
		BizDBAdapter db = new BizDBAdapter(ctx);
		db.openDBConnection().execSQL(sql);
		db.close();
	}

	/**
	 * 插入船源发布新数据
	 * 
	 * @param context
	 * @param shipId
	 * @param state
	 * @param emptyShipPortText
	 * @param emptyShipDateText
	 * @param unitTypeText
	 * @param goodsTypeText
	 * @param startPortText
	 * @param endPortText
	 * @param effectiveDateText
	 * @param minPice
	 * @param maxPice
	 */
	public static void insertIntoTableForSave(Context context, String shipId,
			String state, String emptyShipPortText, String emptyShipDateText,
			String goodsCountUnitTypeText, String goodsTypeText,
			String startPortText, String endPortText, String effectiveDateText,
			String id, String getValid, String getYanQi, String minPice,
			String maxPice) {
		String sql = "INSERT INTO SHIP_ORDER_TABLE (SHIP_ID , STATUS , EMPTY_SHIP_PORT , ENPTY_SHIP_DATE , GOODS_COUNT_UNIT , GOODS_TYPE , START_PORT , END_PORT ,EFFECTIVE_DATE,ID,VALID,YANQI,MIN_PICE,MAX_PICE) VALUES('"
				+ shipId
				+ "','"
				+ state
				+ "','"
				+ emptyShipPortText
				+ "','"
				+ emptyShipDateText
				+ "','"
				+ goodsCountUnitTypeText
				+ "','"
				+ goodsTypeText
				+ "','"
				+ startPortText
				+ "','"
				+ endPortText
				+ "','"
				+ effectiveDateText
				+ "','"
				+ id
				+ "','"
				+ getValid
				+ "','" + getYanQi + "','" + minPice + "','" + maxPice + "') ";
		BizDBAdapter db = new BizDBAdapter(context);
		db.openDBConnection().execSQL(sql);
		db.close();
	}

	/**
	 * 删除非草稿所属的数据
	 * 
	 * @param ctx
	 * @param selectId
	 */
	public static void deleteShipOrderTableByStatus(Context ctx, String status,
			String getId) {
		String sql;
		// 1是平台获取的数据，0是草稿数据
		if ("respond".equals(getId)) {
			// 删除状态为1的数据
			sql = "DELETE FROM SHIP_ORDER_TABLE WHERE STATUS = '" + status
					+ "'";
		} else {
			// 删除指定id而状态为0的数据
			sql = "DELETE FROM SHIP_ORDER_TABLE WHERE STATUS = '" + status
					+ "' AND ID = '" + getId + "'";
		}
		BizDBAdapter db = new BizDBAdapter(ctx);
		db.openDBConnection().execSQL(sql);
		db.close();
	}

	/**
	 * @param ctx
	 * @param followTaskId
	 * @return
	 */
	public static JSONArray selectShipOrderTableByStatus(Context ctx,
			String shipId) {
		String sql = "SELECT * FROM SHIP_ORDER_TABLE WHERE SHIP_ID='" + shipId
				+ "' order by STATUS asc,VALID desc";
		BizDBAdapter db = new BizDBAdapter(ctx);
		Cursor cs = db.openDBConnection().rawQuery(sql, new String[] {});
		JSONArray jdatas = com.mrd.shiptask_shipowner.constants.database.ClassConvertBean
				.returnJsonBeans(cs);
		cs.close();
		db.close();
		return jdatas;
	}

	/***
	 * 存入港口数据
	 * 
	 * @param ctx
	 * @param getPingYin
	 * @param getPort
	 */
	public static void insertIntoTableAboutPorts(Context ctx,
			String getPingYin, String getPort) {
		String sql = "INSERT INTO PORTS (PINGYIN , PORT_NAME ) VALUES('"
				+ getPingYin + "','" + getPort + "') ";
		BizDBAdapter db = new BizDBAdapter(ctx);
		db.openDBConnection().execSQL(sql);
		db.close();
	}

	public static JSONArray selectPorts(Context ctx) {
		try {
			JSONArray jsonArray;
			String sql = "SELECT * FROM PORTS";
			BizDBAdapter db = new BizDBAdapter(ctx);
			Cursor cs = db.openDBConnection().rawQuery(sql, new String[] {});
			jsonArray = com.mrd.shiptask_shipowner.constants.database.ClassConvertBean
					.returnJsonBeans(cs);
			cs.close();
			db.close();
			return jsonArray;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 根据输入的拼音或者文字查询数据
	 * 
	 * @param tHIS
	 * @param getTextOfInfo
	 */
	public static JSONArray selectPortByInfoPingYinOrName(Context ctx,
			String getTextOfInfo) {
		try {
			JSONArray jsonArray;
			String sql = "SELECT * FROM PORTS where PINGYIN like '%"
					+ getTextOfInfo + "%' OR PORT_NAME like '%" + getTextOfInfo
					+ "%'";
			BizDBAdapter db = new BizDBAdapter(ctx);
			Cursor cs = db.openDBConnection().rawQuery(sql, new String[] {});
			jsonArray = com.mrd.shiptask_shipowner.constants.database.ClassConvertBean
					.returnJsonBeans(cs);
			cs.close();
			db.close();
			return jsonArray;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 延期成功后更改延期状态
	 * 
	 * @param ctx
	 * @param followId
	 */
	public static void updateShipOrderYanQiById(Context ctx, String followId) {
		String sql = " UPDATE SHIP_ORDER_TABLE SET YANQI = 'false' WHERE ID = '"
				+ followId + "' ";
		BizDBAdapter db = new BizDBAdapter(ctx);
		db.openDBConnection().execSQL(sql);
		db.close();
	}

	/**
	 * 已配货成功后更改状态
	 * 
	 * @param ctx
	 * @param followId
	 */
	public static void updateShipOrderPeiHuoById(Context ctx, String followId) {
		String sql = " UPDATE SHIP_ORDER_TABLE SET VALID = 'false',YANQI = 'false' WHERE ID = '"
				+ followId + "' ";
		BizDBAdapter db = new BizDBAdapter(ctx);
		db.openDBConnection().execSQL(sql);
		db.close();
	}

	/**
	 * 保存货运申请到本地
	 * 
	 * @param ctx
	 * @param result
	 */
	public static void saveMakeToShipOrderDb(Context ctx, String result) {
		// TODO Auto-generated method stub
		try {
			deleteAll(ctx, "MAKE_TO_ORDER_TABLE");

			result = (new JSONObject(result)).getString("content");
			JSONArray jas = new JSONArray(result);
			for (int i = 0; i < jas.length(); i++) {
				String id = DatasTools.getJsonValue(jas.getJSONObject(i), "id");
				String loadTime = DatasTools.getJsonValue(jas.getJSONObject(i),
						"loadTime");
				String loadRequire = DatasTools.getJsonValue(
						jas.getJSONObject(i), "loadRequire");
				String code = DatasTools.getJsonValue(jas.getJSONObject(i),
						"code");
				String cargoName = DatasTools.getJsonValue(
						jas.getJSONObject(i), "cargoName");
				String startPort = DatasTools.getJsonValue(
						jas.getJSONObject(i), "startPort");
				String endPort = DatasTools.getJsonValue(jas.getJSONObject(i),
						"endPort");
				String rAmount = DatasTools.getJsonValue(jas.getJSONObject(i),
						"rAmount");
				String cargoUnits = DatasTools.getJsonValue(
						jas.getJSONObject(i), "cargoUnits");
				String includePCF = DatasTools.getJsonValue(
						jas.getJSONObject(i), "includePCF");
				String accountMethod = DatasTools.getJsonValue(
						jas.getJSONObject(i), "accountMethod");
				String packingType = DatasTools.getJsonValue(
						jas.getJSONObject(i), "packingType");
				String consignorCompany = DatasTools.getJsonValue(
						jas.getJSONObject(i), "consignorCompany");
				String consignor = DatasTools.getJsonValue(
						jas.getJSONObject(i), "consignor");
				String contact = DatasTools.getJsonValue(jas.getJSONObject(i),
						"contact");
				String expectPrice = DatasTools.getJsonValue(
						jas.getJSONObject(i), "expectPrice");
				String fareTax = DatasTools.getJsonValue(jas.getJSONObject(i),
						"fareTax");
				String lossRate = DatasTools.getJsonValue(jas.getJSONObject(i),
						"lossRate");
				String loadUseTime = DatasTools.getJsonValue(
						jas.getJSONObject(i), "loadUseTime");
				String demurrage = DatasTools.getJsonValue(
						jas.getJSONObject(i), "demurrage");
				String shipTonnages = DatasTools.getJsonValue(
						jas.getJSONObject(i), "shipTonnages");
				String remark = DatasTools.getJsonValue(jas.getJSONObject(i),
						"remark");
				String sql = "INSERT INTO MAKE_TO_ORDER_TABLE (ID ,LOAD_TIME,LOAD_REQUIRE,CODE,CARGO_NAME,START_PORT,END_PORT,R_AMOUNT,CARGO_UNITS,INCLUDE_PCF,ACCOUNT_METHOD, PACKING_TYPE,CONSIGNOR_COMPANY,CONSIGNOR,CONTACT,EXPECT_PRICE,FARE_TAX,LOSS_RATE, LOAD_USE_TIME,DEMURRAGE,SHIP_TONNAGES,REMARK) VALUES('"
						+ id
						+ "','"
						+ loadTime
						+ "','"
						+ loadRequire
						+ "','"
						+ code
						+ "','"
						+ cargoName
						+ "','"
						+ startPort
						+ "','"
						+ endPort
						+ "','"
						+ rAmount
						+ "','"
						+ cargoUnits
						+ "','"
						+ includePCF
						+ "','"
						+ accountMethod
						+ "','"
						+ packingType
						+ "','"
						+ consignorCompany
						+ "','"
						+ consignor
						+ "','"
						+ contact
						+ "','"
						+ expectPrice
						+ "','"
						+ fareTax
						+ "','"
						+ lossRate
						+ "','"
						+ loadUseTime
						+ "','"
						+ demurrage
						+ "','"
						+ shipTonnages + "','" + remark + "') ";
				BizDBAdapter db = new BizDBAdapter(ctx);
				db.openDBConnection().execSQL(sql);
				db.close();
			}

		} catch (Exception e) {
		}
	}

	/**
	 * 查询货运申请菜单
	 * 
	 * @param ctx
	 * @return
	 */
	public static JSONArray selectMakeToShipOrderTable(Context ctx) {
		// TODO Auto-generated method stub
		try {
			JSONArray jsonArray;
			String sql = "SELECT * FROM MAKE_TO_ORDER_TABLE";
			BizDBAdapter db = new BizDBAdapter(ctx);
			Cursor cs = db.openDBConnection().rawQuery(sql, new String[] {});
			jsonArray = com.mrd.shiptask_shipowner.constants.database.ClassConvertBean
					.returnJsonBeans(cs);
			cs.close();
			db.close();
			return jsonArray;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 保存运输合同信息
	 * 
	 * @param ctx
	 * @param result
	 */
	public static void saveElectronManifestDb(Context ctx, String result) {
		// TODO Auto-generated method stub
		try {
			deleteAll(ctx, "ElECTRON_MANIFEST_TABLE");
			System.out.println(result);
			result = (new JSONObject(result)).getString("content");
			JSONArray jas = new JSONArray(result);
			for (int i = 0; i < jas.length(); i++) {
				String id = DatasTools.getJsonValue(jas.getJSONObject(i), "id");// id
				// 子订单号
				String code = DatasTools.getJsonValue(jas.getJSONObject(i),
						"code");
				// 托运人
				String shipper = DatasTools.getJsonValue(jas.getJSONObject(i),
						"consignorName");
				// 货物
				String goods = DatasTools.getJsonValue(jas.getJSONObject(i),
						"goods");
				// 货物数量单位
				String goodsNameAndUnit = DatasTools.getJsonValue(
						jas.getJSONObject(i), "amount");
				// 目的港
				String end_port = DatasTools.getJsonValue(jas.getJSONObject(i),
						"endPort");
				// 起运地
				String start_port = DatasTools.getJsonValue(
						jas.getJSONObject(i), "startPort");
				// 截止日期
				String clossing_date = DatasTools.getJsonValue(
						jas.getJSONObject(i), "loadFinishTime");
				// 船东确认状态
				String shipper_confirm = DatasTools.getJsonValue(
						jas.getJSONObject(i), "shipperConfirm");
				// 货主确认状态
				String owner_confirm = DatasTools.getJsonValue(
						jas.getJSONObject(i), "ownerConfirm");
				// 货主状态为2.取消的时候，船东默认为1.同意取消
				if ("2".equals(owner_confirm)) {
					shipper_confirm = "1";
				}
				String sql = "INSERT INTO ElECTRON_MANIFEST_TABLE(ID,CODE,SHIPPER,GOODS,AMOUNT, END_PORT,START_PORT, CLOSSING_DATE,SHIPPER_CONFIRM,OWNER_CONFIRM) VALUES('"
						+ id
						+ "','"
						+ code
						+ "','"
						+ shipper
						+ "','"
						+ goods
						+ "','"
						+ goodsNameAndUnit
						+ "','"
						+ end_port
						+ "','"
						+ start_port
						+ "','"
						+ clossing_date
						+ "','"
						+ shipper_confirm + "','" + owner_confirm + "') ";
				BizDBAdapter db = new BizDBAdapter(ctx);
				db.openDBConnection().execSQL(sql);
				db.close();
			}

		} catch (Exception e) {
		}

	}

	/**
	 * 保存运输合同单条信息
	 * 
	 * @param ctx
	 * @param result
	 */
	public static void saveElectronManifestDbForOne(Context ctx, String result) {
		// TODO Auto-generated method stub
		try {

			JSONObject job = new JSONObject(result);
			String id = DatasTools.getJsonValue(job, "id");// id
			// 子订单号
			String code = DatasTools.getJsonValue(job, "code");
			// 托运人
			String shipper = DatasTools.getJsonValue(job, "consignorName");
			// 货物
			String goods = DatasTools.getJsonValue(job, "goods");
			// 货物数量单位
			String consignorName = DatasTools
					.getJsonValue(job, "consignorName");
			// 目的港
			String end_port = DatasTools.getJsonValue(job, "endPort");
			// 起运地
			String start_port = DatasTools.getJsonValue(job, "startPort");
			// 截止日期
			String clossing_date = DatasTools.getJsonValue(job,
					"loadFinishTime");
			// 船东确认状态
			String shipper_confirm = DatasTools.getJsonValue(job,
					"shipperConfirm");
			// 货主确认状态
			String owner_confirm = DatasTools.getJsonValue(job, "ownerConfirm");

			String sql = "INSERT INTO ElECTRON_MANIFEST_TABLE(ID,CODE,SHIPPER,GOODS,AMOUNT, END_PORT,START_PORT, CLOSSING_DATE,SHIPPER_CONFIRM,OWNER_CONFIRM) VALUES('"
					+ id
					+ "','"
					+ code
					+ "','"
					+ shipper
					+ "','"
					+ goods
					+ "','"
					+ consignorName
					+ "','"
					+ end_port
					+ "','"
					+ start_port
					+ "','"
					+ clossing_date
					+ "','"
					+ shipper_confirm + "','" + owner_confirm + "') ";
			BizDBAdapter db = new BizDBAdapter(ctx);
			db.openDBConnection().execSQL(sql);
			db.close();

		} catch (Exception e) {
		}
	}

	public static JSONArray selectElectronTable(Context ctx, boolean b) {
		// TODO Auto-generated method stub
		try {
			JSONArray jsonArray;
			String sql = "";
			if (b) {
				sql = "SELECT * FROM ElECTRON_MANIFEST_TABLE WHERE SHIPPER_CONFIRM ='1' AND OWNER_CONFIRM IN ('1','2')";
			} else {
				sql = "SELECT * FROM ElECTRON_MANIFEST_TABLE WHERE SHIPPER_CONFIRM in ('0','2') OR OWNER_CONFIRM in ('0','3')";
			}
			BizDBAdapter db = new BizDBAdapter(ctx);
			Cursor cs = db.openDBConnection().rawQuery(sql, new String[] {});
			jsonArray = com.mrd.shiptask_shipowner.constants.database.ClassConvertBean
					.returnJsonBeans(cs);
			cs.close();
			db.close();
			return jsonArray;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 保存确认签单信息
	 * 
	 * @param ctx
	 * @param result
	 */
	public static void saveConfirmOrderDb(Context ctx, String result) {
		// TODO Auto-generated method stub
		try {
			deleteAll(ctx, "CONFIRM_ORDER_TABLE");

			result = (new JSONObject(result)).getString("content");
			JSONArray jas = new JSONArray(result);
			for (int i = 0; i < jas.length(); i++) {
				String id = DatasTools.getJsonValue(jas.getJSONObject(i), "id");// id
				// 子订单号
				String code = DatasTools.getJsonValue(jas.getJSONObject(i),
						"code");
				// 托运人
				String shipper = DatasTools.getJsonValue(jas.getJSONObject(i),
						"consignorName");
				// 货物
				String goodsName = DatasTools.getJsonValue(
						jas.getJSONObject(i), "goods");
				// 货物单位
				String goodsUnit = DatasTools.getJsonValue(
						jas.getJSONObject(i), "goodsUnit");
				// 货物数量
				String goodsCount = DatasTools.getJsonValue(
						jas.getJSONObject(i), "goodsAmount");
				// 截止日期
				String clossingDate = DatasTools.getJsonValue(
						jas.getJSONObject(i), "loadFinishTime");
				// 装货港
				String loadingPort = DatasTools.getJsonValue(
						jas.getJSONObject(i), "loadingPort");
				// 卸货港
				String dischargePort = DatasTools.getJsonValue(
						jas.getJSONObject(i), "dischargePort");
				// 装货数
				String loadingCount = DatasTools.getJsonValue(
						jas.getJSONObject(i), "loadAmount");
				// 卸货数
				String dischargeCount = DatasTools.getJsonValue(
						jas.getJSONObject(i), "dischargeAmount");
				// 签单是否完成
				String finish = DatasTools.getJsonValue(jas.getJSONObject(i),
						"finish");
				// 实卸数
				String shipperConfirmAmount = DatasTools.getJsonValue(
						jas.getJSONObject(i), "shipperConfirmAmount");
				// 船东是否确认
				String shipperConfirm = DatasTools.getJsonValue(
						jas.getJSONObject(i), "shipperConfirm");
				String sql = "INSERT INTO CONFIRM_ORDER_TABLE(ID,CODE,SHIPPER, GOODS,GOODS_COUNT,GOODS_UNIT,CLOSSING_DATE,LOADING_PORT,DISCHARGE_PORT, LOADING_COUNT, DISCHARGE_COUNT, SHIPPER_CONFIRM,FINISH,SHIPPER_CONFIRM_AMOUNT) VALUES('"
						+ id
						+ "','"
						+ code
						+ "','"
						+ shipper
						+ "','"
						+ goodsName
						+ "','"
						+ goodsCount
						+ "','"
						+ goodsUnit
						+ "','"
						+ clossingDate
						+ "','"
						+ loadingPort
						+ "','"
						+ dischargePort
						+ "','"
						+ loadingCount
						+ "','"
						+ dischargeCount
						+ "','"
						+ shipperConfirm
						+ "','"
						+ finish + "','" + shipperConfirmAmount + "') ";
				BizDBAdapter db = new BizDBAdapter(ctx);
				db.openDBConnection().execSQL(sql);
				db.close();
			}

		} catch (Exception e) {
		}
	}

	public static JSONArray selectConfirmOrderTable(Context ctx, boolean b) {
		// TODO Auto-generated method stub
		try {
			JSONArray jsonArray;
			String sql = "";
			if (b) {
				sql = "SELECT * FROM CONFIRM_ORDER_TABLE WHERE FINISH = 'true'";
			} else {
				sql = "SELECT * FROM CONFIRM_ORDER_TABLE WHERE FINISH = 'false'";
			}
			BizDBAdapter db = new BizDBAdapter(ctx);
			Cursor cs = db.openDBConnection().rawQuery(sql, new String[] {});
			jsonArray = com.mrd.shiptask_shipowner.constants.database.ClassConvertBean
					.returnJsonBeans(cs);
			cs.close();
			db.close();
			return jsonArray;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 获取运输合同的船东或者货主状态
	 * 
	 * @param ctx
	 * @param eleId
	 * @param type
	 * @return
	 */
	public static String getConfirmFromDBByType(Context ctx, String eleId,
			String type) {
		// TODO Auto-generated method stub
		try {
			JSONArray jsonArray;
			String sql = "SELECT * FROM ElECTRON_MANIFEST_TABLE WHERE ID = '"
					+ eleId + "'";
			BizDBAdapter db = new BizDBAdapter(ctx);
			Cursor cs = db.openDBConnection().rawQuery(sql, new String[] {});
			jsonArray = com.mrd.shiptask_shipowner.constants.database.ClassConvertBean
					.returnJsonBeans(cs);
			cs.close();
			db.close();
			if ("shipper".equals(type)) {
				return jsonArray.getJSONObject(0).getString("SHIPPER_CONFIRM");
			} else if ("ownner".equals(type)) {
				return jsonArray.getJSONObject(0).getString("OWNER_CONFIRM");
			} else {
				return null;
			}
		} catch (Exception e) {
			return null;
		}
	}

}
